import pymysql


class Database:

    # 对这个数据库进行判断, 如果不存在需要的那两站表，则自动进行创建
    def __init__(self):
        self.db = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='12345678',
            database='informations'
        )
        curses = self.db.cursor()
        # 如果表不存在则进行判断
        sql1 = "CREATE TABLE IF NOT EXISTS usersheet (id INT NOT NULL,name VARCHAR(20) NOT NULL,identity INT NOT NULL," \
               "PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8"
        sql2 = "CREATE TABLE IF NOT EXISTS signform (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20) NOT NULL,time DATETIME NOT NULL," \
               "PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8"
        curses.execute(sql1)
        curses.execute(sql2)

    def add_one(self, ids, name, identity):
        cursor = self.db.cursor()
        sql1 = "INSERT INTO usersheet(id, name, identity) VALUES ({0}, '{1}', {2})".format(ids, name, identity)
        cursor.execute(sql1)
        self.db.commit()

    def check_exit(self, name):
        cursor = self.db.cursor()
        sql1 = "SELECT name FROM usersheet WHERE name='{}'".format(name)
        is_exit = cursor.execute(sql1)
        return is_exit

    def get_next_id(self):
        cursor = self.db.cursor()
        sql1 = "SELECT id FROM usersheet"
        cursor.execute(sql1)
        datas = cursor.fetchall()
        max_id = 0
        for data in datas:
            if data[0] > max_id:
                max_id = data[0]
        return max_id + 1

    def delete_one(self, name):
        # 执行删除操作
        cursor = self.db.cursor()
        sql1 = "DELETE FROM usersheet WHERE name='{}'".format(name)
        cursor.execute(sql1)
        self.db.commit()

    def add_sign(self, name):
        cursor = self.db.cursor()
        sql1 = "INSERT INTO signform(name, time) VALUES ('{0}',NOW())".format(name)
        cursor.execute(sql1)
        self.db.commit()

    def get_names_from_time(self, first, second):
        cursor = self.db.cursor()
        sql1 = "SELECT name,time FROM signform WHERE time between '{}' and '{}'".format(str(first), str(second))
        cursor.execute(sql1)
        sign_in_information = cursor.fetchall()
        return sign_in_information

    def get_info_by_id(self, id_num):
        cursor = self.db.cursor()
        sql1 = "SELECT id, name, identity FROM usersheet WHERE id={}".format(id_num)
        cursor.execute(sql1)
        dates = cursor.fetchall()
        return dates

    def get_id_by_name(self, name):
        cursor = self.db.cursor()
        sql1 = "SELECT id FROM usersheet WHERE name='{}'".format(name)
        cursor.execute(sql1)
        datas = cursor.fetchall()
        return datas

    def get_all_user_information(self):
        cursor = self.db.cursor()
        sql1 = "SELECT * FROM usersheet"
        cursor.execute(sql1)
        datas = cursor.fetchall()
        return datas



database = Database()

